DROP FUNCTION [dbo].[fn_splitStr]
GO
DROP FUNCTION [dbo].[fn_replaceNum]
GO
DROP FUNCTION [dbo].[fn_getPy]
GO
DROP FUNCTION [dbo].[fn_getPinyin]
GO
DROP FUNCTION [dbo].[fn_getDis]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---创建经纬度距离计算函数
---LatBegin 开始经度
--LngBegin 开始维度
create function [dbo].[fn_getDis](@latS decimal(18, 8)
, @lngS decimal(18, 8)
, @latE decimal(18, 8)
, @lngE decimal(18, 8))
returns decimal(18, 2)
as begin
    if(@latS is null)set @latS = 0;
    if(@lngS is null)set @lngS = 0;
    if(@latE is null)set @latE = 0;
    if(@lngE is null)set @lngE = 0;
    --距离(千米)
    declare @dis decimal(18, 8);
    declare @earthRadius decimal(18, 8) = 6378.137;
    declare @radLatS decimal(18, 8), @radLatE decimal(18, 8), @radLatDiff decimal(18, 8), @radLngDiff decimal(18, 8);
    set @radLatS = @latS * pi()/ 180.0;
    set @radLatE = @latE * pi()/ 180.0;
    set @radLatDiff = @radLatS-@radLatE;
    set @radLngDiff = @lngS * pi()/ 180.0-@lngE * pi()/ 180.0;
    set @dis = 2 * asin(sqrt(power(sin(@radLatDiff / 2), 2)+cos(@radLatS)* cos(@radLatE)* power(sin(@radLngDiff / 2), 2)));
    set @dis = @dis * @earthRadius;
    --SET @dis = Round(@dis * 10000) / 10000 
    return @dis; --@dis的单位为:千米
end;
GO
/****** Object:  UserDefinedFunction [dbo].[fn_getPinyin]    Script Date: 2022/2/15 11:52:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[fn_getPinyin](@words varchar(8000))
returns varchar(8000)
as begin
    declare @word nchar(1);
    declare @pinyin varchar(8000);
    declare @i int;
    declare @words_len int;
    declare @unicode int;
    set @i = 1;
    set @words = ltrim(rtrim(@words));
    set @words_len = len(@words);
    while(@i <= @words_len) --循环取字符  
    begin
        set @word = substring(@words, @i, 1);
        set @unicode = unicode(@word);
        set @pinyin = isnull(@pinyin, '')+(case when unicode(@word) between 19968 and 19968+20901 then (select top 1 py
                                                                                                        from(select 'a' as py, N'厑' as word
                                                                                                             union all
                                                                                                             select 'ai', N'靉'
                                                                                                             union all
                                                                                                             select 'an', N'黯'
                                                                                                             union all
                                                                                                             select 'ang', N'醠'
                                                                                                             union all
                                                                                                             select 'ao', N'驁'
                                                                                                             union all
                                                                                                             select 'ba', N'欛'
                                                                                                             union all
                                                                                                             select 'bai', N'瓸' --韛兡瓸  
                                                                                                             union all
                                                                                                             select 'ban', N'瓣'
                                                                                                             union all
                                                                                                             select 'bang', N'鎊'
                                                                                                             union all
                                                                                                             select 'bao', N'鑤'
                                                                                                             union all
                                                                                                             select 'bei', N'鐾'
                                                                                                             union all
                                                                                                             select 'ben', N'輽'
                                                                                                             union all
                                                                                                             select 'beng', N'鏰'
                                                                                                             union all
                                                                                                             select 'bi', N'鼊'
                                                                                                             union all
                                                                                                             select 'bian', N'變'
                                                                                                             union all
                                                                                                             select 'biao', N'鰾'
                                                                                                             union all
                                                                                                             select 'bie', N'彆'
                                                                                                             union all
                                                                                                             select 'bin', N'鬢'
                                                                                                             union all
                                                                                                             select 'bing', N'靐'
                                                                                                             union all
                                                                                                             select 'bo', N'蔔'
                                                                                                             union all
                                                                                                             select 'bu', N'簿'
                                                                                                             union all
                                                                                                             select 'ca', N'囃'
                                                                                                             union all
                                                                                                             select 'cai', N'乲' --縩乲  
                                                                                                             union all
                                                                                                             select 'can', N'爘'
                                                                                                             union all
                                                                                                             select 'cang', N'賶'
                                                                                                             union all
                                                                                                             select 'cao', N'鼜'
                                                                                                             union all
                                                                                                             select 'ce', N'簎'
                                                                                                             union all
                                                                                                             select 'cen', N'笒'
                                                                                                             union all
                                                                                                             select 'ceng', N'乽' --硛硳岾猠乽  
                                                                                                             union all
                                                                                                             select 'cha', N'詫'
                                                                                                             union all
                                                                                                             select 'chai', N'囆'
                                                                                                             union all
                                                                                                             select 'chan', N'顫'
                                                                                                             union all
                                                                                                             select 'chang', N'韔'
                                                                                                             union all
                                                                                                             select 'chao', N'觘'
                                                                                                             union all
                                                                                                             select 'che', N'爡'
                                                                                                             union all
                                                                                                             select 'chen', N'讖'
                                                                                                             union all
                                                                                                             select 'cheng', N'秤'
                                                                                                             union all
                                                                                                             select 'chi', N'鷘'
                                                                                                             union all
                                                                                                             select 'chong', N'銃'
                                                                                                             union all
                                                                                                             select 'chou', N'殠'
                                                                                                             union all
                                                                                                             select 'chu', N'矗'
                                                                                                             union all
                                                                                                             select 'chuai', N'踹'
                                                                                                             union all
                                                                                                             select 'chuan', N'鶨'
                                                                                                             union all
                                                                                                             select 'chuang', N'愴'
                                                                                                             union all
                                                                                                             select 'chui', N'顀'
                                                                                                             union all
                                                                                                             select 'chun', N'蠢'
                                                                                                             union all
                                                                                                             select 'chuo', N'縒'
                                                                                                             union all
                                                                                                             select 'ci', N'嗭' --賜嗭  
                                                                                                             union all
                                                                                                             select 'cong', N'謥'
                                                                                                             union all
                                                                                                             select 'cou', N'輳'
                                                                                                             union all
                                                                                                             select 'cu', N'顣'
                                                                                                             union all
                                                                                                             select 'cuan', N'爨'
                                                                                                             union all
                                                                                                             select 'cui', N'臎'
                                                                                                             union all
                                                                                                             select 'cun', N'籿'
                                                                                                             union all
                                                                                                             select 'cuo', N'錯'
                                                                                                             union all
                                                                                                             select 'da', N'橽'
                                                                                                             union all
                                                                                                             select 'dai', N'靆'
                                                                                                             union all
                                                                                                             select 'dan', N'饏'
                                                                                                             union all
                                                                                                             select 'dang', N'闣'
                                                                                                             union all
                                                                                                             select 'dao', N'纛'
                                                                                                             union all
                                                                                                             select 'de', N'的'
                                                                                                             union all
                                                                                                             select 'den', N'扽'
                                                                                                             union all
                                                                                                             select 'deng', N'鐙'
                                                                                                             union all
                                                                                                             select 'di', N'螮'
                                                                                                             union all
                                                                                                             select 'dia', N'嗲'
                                                                                                             union all
                                                                                                             select 'dian', N'驔'
                                                                                                             union all
                                                                                                             select 'diao', N'鑃'
                                                                                                             union all
                                                                                                             select 'die', N'嚸' --眰嚸  
                                                                                                             union all
                                                                                                             select 'ding', N'顁'
                                                                                                             union all
                                                                                                             select 'diu', N'銩'
                                                                                                             union all
                                                                                                             select 'dong', N'霘'
                                                                                                             union all
                                                                                                             select 'dou', N'鬭'
                                                                                                             union all
                                                                                                             select 'du', N'蠹'
                                                                                                             union all
                                                                                                             select 'duan', N'叾' --籪叾  
                                                                                                             union all
                                                                                                             select 'dui', N'譵'
                                                                                                             union all
                                                                                                             select 'dun', N'踲'
                                                                                                             union all
                                                                                                             select 'duo', N'鵽'
                                                                                                             union all
                                                                                                             select 'e', N'鱷'
                                                                                                             union all
                                                                                                             select 'en', N'摁'
                                                                                                             union all
                                                                                                             select 'eng', N'鞥'
                                                                                                             union all
                                                                                                             select 'er', N'樲'
                                                                                                             union all
                                                                                                             select 'fa', N'髮'
                                                                                                             union all
                                                                                                             select 'fan', N'瀪'
                                                                                                             union all
                                                                                                             select 'fang', N'放'
                                                                                                             union all
                                                                                                             select 'fei', N'靅'
                                                                                                             union all
                                                                                                             select 'fen', N'鱝'
                                                                                                             union all
                                                                                                             select 'feng', N'覅'
                                                                                                             union all
                                                                                                             select 'fo', N'梻'
                                                                                                             union all
                                                                                                             select 'fou', N'鴀'
                                                                                                             union all
                                                                                                             select 'fu', N'猤' --鰒猤  
                                                                                                             union all
                                                                                                             select 'ga', N'魀'
                                                                                                             union all
                                                                                                             select 'gai', N'瓂'
                                                                                                             union all
                                                                                                             select 'gan', N'灨'
                                                                                                             union all
                                                                                                             select 'gang', N'戇'
                                                                                                             union all
                                                                                                             select 'gao', N'鋯'
                                                                                                             union all
                                                                                                             select 'ge', N'獦'
                                                                                                             union all
                                                                                                             select 'gei', N'給'
                                                                                                             union all
                                                                                                             select 'gen', N'搄'
                                                                                                             union all
                                                                                                             select 'geng', N'堩' --亙堩啹喼嗰  
                                                                                                             union all
                                                                                                             select 'gong', N'兣' --熕贑兝兣  
                                                                                                             union all
                                                                                                             select 'gou', N'購'
                                                                                                             union all
                                                                                                             select 'gu', N'顧'
                                                                                                             union all
                                                                                                             select 'gua', N'詿'
                                                                                                             union all
                                                                                                             select 'guai', N'恠'
                                                                                                             union all
                                                                                                             select 'guan', N'鱹'
                                                                                                             union all
                                                                                                             select 'guang', N'撗'
                                                                                                             union all
                                                                                                             select 'gui', N'鱥'
                                                                                                             union all
                                                                                                             select 'gun', N'謴'
                                                                                                             union all
                                                                                                             select 'guo', N'腂'
                                                                                                             union all
                                                                                                             select 'ha', N'哈'
                                                                                                             union all
                                                                                                             select 'hai', N'饚'
                                                                                                             union all
                                                                                                             select 'han', N'鶾'
                                                                                                             union all
                                                                                                             select 'hang', N'沆'
                                                                                                             union all
                                                                                                             select 'hao', N'兞'
                                                                                                             union all
                                                                                                             select 'he', N'靏'
                                                                                                             union all
                                                                                                             select 'hei', N'嬒'
                                                                                                             union all
                                                                                                             select 'hen', N'恨'
                                                                                                             union all
                                                                                                             select 'heng', N'堼' --堼囍  
                                                                                                             union all
                                                                                                             select 'hong', N'鬨'
                                                                                                             union all
                                                                                                             select 'hou', N'鱟'
                                                                                                             union all
                                                                                                             select 'hu', N'鸌'
                                                                                                             union all
                                                                                                             select 'hua', N'蘳'
                                                                                                             union all
                                                                                                             select 'huai', N'蘾'
                                                                                                             union all
                                                                                                             select 'huan', N'鰀'
                                                                                                             union all
                                                                                                             select 'huang', N'鎤'
                                                                                                             union all
                                                                                                             select 'hui', N'顪'
                                                                                                             union all
                                                                                                             select 'hun', N'諢'
                                                                                                             union all
                                                                                                             select 'huo', N'夻'
                                                                                                             union all
                                                                                                             select 'ji', N'驥'
                                                                                                             union all
                                                                                                             select 'jia', N'嗧'
                                                                                                             union all
                                                                                                             select 'jian', N'鑳'
                                                                                                             union all
                                                                                                             select 'jiang', N'謽'
                                                                                                             union all
                                                                                                             select 'jiao', N'釂'
                                                                                                             union all
                                                                                                             select 'jie', N'繲'
                                                                                                             union all
                                                                                                             select 'jin', N'齽'
                                                                                                             union all
                                                                                                             select 'jing', N'竸'
                                                                                                             union all
                                                                                                             select 'jiong', N'蘔'
                                                                                                             union all
                                                                                                             select 'jiu', N'欍'
                                                                                                             union all
                                                                                                             select 'ju', N'爠'
                                                                                                             union all
                                                                                                             select 'juan', N'羂'
                                                                                                             union all
                                                                                                             select 'jue', N'钁'
                                                                                                             union all
                                                                                                             select 'jun', N'攈'
                                                                                                             union all
                                                                                                             select 'ka', N'鉲'
                                                                                                             union all
                                                                                                             select 'kai', N'乫' --鎎乫  
                                                                                                             union all
                                                                                                             select 'kan', N'矙'
                                                                                                             union all
                                                                                                             select 'kang', N'閌'
                                                                                                             union all
                                                                                                             select 'kao', N'鯌'
                                                                                                             union all
                                                                                                             select 'ke', N'騍'
                                                                                                             union all
                                                                                                             select 'ken', N'褃'
                                                                                                             union all
                                                                                                             select 'keng', N'鏗' --巪乬唟厼怾  
                                                                                                             union all
                                                                                                             select 'kong', N'廤'
                                                                                                             union all
                                                                                                             select 'kou', N'鷇'
                                                                                                             union all
                                                                                                             select 'ku', N'嚳'
                                                                                                             union all
                                                                                                             select 'kua', N'骻'
                                                                                                             union all
                                                                                                             select 'kuai', N'鱠'
                                                                                                             union all
                                                                                                             select 'kuan', N'窾'
                                                                                                             union all
                                                                                                             select 'kuang', N'鑛'
                                                                                                             union all
                                                                                                             select 'kui', N'鑎'
                                                                                                             union all
                                                                                                             select 'kun', N'睏'
                                                                                                             union all
                                                                                                             select 'kuo', N'穒'
                                                                                                             union all
                                                                                                             select 'la', N'鞡'
                                                                                                             union all
                                                                                                             select 'lai', N'籟'
                                                                                                             union all
                                                                                                             select 'lan', N'糷'
                                                                                                             union all
                                                                                                             select 'lang', N'唥'
                                                                                                             union all
                                                                                                             select 'lao', N'軂'
                                                                                                             union all
                                                                                                             select 'le', N'餎'
                                                                                                             union all
                                                                                                             select 'lei', N'脷' --嘞脷  
                                                                                                             union all
                                                                                                             select 'leng', N'睖'
                                                                                                             union all
                                                                                                             select 'li', N'瓈'
                                                                                                             union all
                                                                                                             select 'lia', N'倆'
                                                                                                             union all
                                                                                                             select 'lian', N'纞'
                                                                                                             union all
                                                                                                             select 'liang', N'鍄'
                                                                                                             union all
                                                                                                             select 'liao', N'瞭'
                                                                                                             union all
                                                                                                             select 'lie', N'鱲'
                                                                                                             union all
                                                                                                             select 'lin', N'轥' --轥拎  
                                                                                                             union all
                                                                                                             select 'ling', N'炩'
                                                                                                             union all
                                                                                                             select 'liu', N'咯' --瓼甅囖咯  
                                                                                                             union all
                                                                                                             select 'long', N'贚'
                                                                                                             union all
                                                                                                             select 'lou', N'鏤'
                                                                                                             union all
                                                                                                             select 'lu', N'氇'
                                                                                                             union all
                                                                                                             select 'lv', N'鑢'
                                                                                                             union all
                                                                                                             select 'luan', N'亂'
                                                                                                             union all
                                                                                                             select 'lue', N'擽'
                                                                                                             union all
                                                                                                             select 'lun', N'論'
                                                                                                             union all
                                                                                                             select 'luo', N'鱳'
                                                                                                             union all
                                                                                                             select 'ma', N'嘛'
                                                                                                             union all
                                                                                                             select 'mai', N'霢'
                                                                                                             union all
                                                                                                             select 'man', N'蘰'
                                                                                                             union all
                                                                                                             select 'mang', N'蠎'
                                                                                                             union all
                                                                                                             select 'mao', N'唜'
                                                                                                             union all
                                                                                                             select 'me', N'癦' --癦呅  
                                                                                                             union all
                                                                                                             select 'mei', N'嚜'
                                                                                                             union all
                                                                                                             select 'men', N'們'
                                                                                                             union all
                                                                                                             select 'meng', N'霥' --霿踎  
                                                                                                             union all
                                                                                                             select 'mi', N'羃'
                                                                                                             union all
                                                                                                             select 'mian', N'麵'
                                                                                                             union all
                                                                                                             select 'miao', N'廟'
                                                                                                             union all
                                                                                                             select 'mie', N'鱴' --鱴瓱  
                                                                                                             union all
                                                                                                             select 'min', N'鰵'
                                                                                                             union all
                                                                                                             select 'ming', N'詺'
                                                                                                             union all
                                                                                                             select 'miu', N'謬'
                                                                                                             union all
                                                                                                             select 'mo', N'耱' --耱乮  
                                                                                                             union all
                                                                                                             select 'mou', N'麰' --麰蟱  
                                                                                                             union all
                                                                                                             select 'mu', N'旀'
                                                                                                             union all
                                                                                                             select 'na', N'魶'
                                                                                                             union all
                                                                                                             select 'nai', N'錼'
                                                                                                             union all
                                                                                                             select 'nan', N'婻'
                                                                                                             union all
                                                                                                             select 'nang', N'齉'
                                                                                                             union all
                                                                                                             select 'nao', N'臑'
                                                                                                             union all
                                                                                                             select 'ne', N'呢'
                                                                                                             union all
                                                                                                             select 'nei', N'焾' --嫩焾  
                                                                                                             union all
                                                                                                             select 'nen', N'嫩'
                                                                                                             union all
                                                                                                             select 'neng', N'能' --莻嗯鈪銰啱  
                                                                                                             union all
                                                                                                             select 'ni', N'嬺'
                                                                                                             union all
                                                                                                             select 'nian', N'艌'
                                                                                                             union all
                                                                                                             select 'niang', N'釀'
                                                                                                             union all
                                                                                                             select 'niao', N'脲'
                                                                                                             union all
                                                                                                             select 'nie', N'钀'
                                                                                                             union all
                                                                                                             select 'nin', N'拰'
                                                                                                             union all
                                                                                                             select 'ning', N'濘'
                                                                                                             union all
                                                                                                             select 'niu', N'靵'
                                                                                                             union all
                                                                                                             select 'nong', N'齈'
                                                                                                             union all
                                                                                                             select 'nou', N'譳'
                                                                                                             union all
                                                                                                             select 'nu', N'搙'
                                                                                                             union all
                                                                                                             select 'nv', N'衄'
                                                                                                             union all
                                                                                                             select 'nue', N'瘧'
                                                                                                             union all
                                                                                                             select 'nuan', N'燶' --硸黁燶郍  
                                                                                                             union all
                                                                                                             select 'nuo', N'桛'
                                                                                                             union all
                                                                                                             select 'o', N'鞰' --毮夞乯鞰  
                                                                                                             union all
                                                                                                             select 'ou', N'漚'
                                                                                                             union all
                                                                                                             select 'pa', N'袙'
                                                                                                             union all
                                                                                                             select 'pai', N'磗' --鎃磗  
                                                                                                             union all
                                                                                                             select 'pan', N'鑻'
                                                                                                             union all
                                                                                                             select 'pang', N'胖'
                                                                                                             union all
                                                                                                             select 'pao', N'礮'
                                                                                                             union all
                                                                                                             select 'pei', N'轡'
                                                                                                             union all
                                                                                                             select 'pen', N'喯'
                                                                                                             union all
                                                                                                             select 'peng', N'喸' --浌巼闏乶喸  
                                                                                                             union all
                                                                                                             select 'pi', N'鸊'
                                                                                                             union all
                                                                                                             select 'pian', N'騙'
                                                                                                             union all
                                                                                                             select 'piao', N'慓'
                                                                                                             union all
                                                                                                             select 'pie', N'嫳'
                                                                                                             union all
                                                                                                             select 'pin', N'聘'
                                                                                                             union all
                                                                                                             select 'ping', N'蘋'
                                                                                                             union all
                                                                                                             select 'po', N'魄'
                                                                                                             union all
                                                                                                             select 'pou', N'哛' --兺哛  
                                                                                                             union all
                                                                                                             select 'pu', N'曝'
                                                                                                             union all
                                                                                                             select 'qi', N'蟿'
                                                                                                             union all
                                                                                                             select 'qia', N'髂'
                                                                                                             union all
                                                                                                             select 'qian', N'縴'
                                                                                                             union all
                                                                                                             select 'qiang', N'瓩' --羻兛瓩  
                                                                                                             union all
                                                                                                             select 'qiao', N'躈'
                                                                                                             union all
                                                                                                             select 'qie', N'籡'
                                                                                                             union all
                                                                                                             select 'qin', N'藽'
                                                                                                             union all
                                                                                                             select 'qing', N'櫦'
                                                                                                             union all
                                                                                                             select 'qiong', N'瓗'
                                                                                                             union all
                                                                                                             select 'qiu', N'糗'
                                                                                                             union all
                                                                                                             select 'qu', N'覻'
                                                                                                             union all
                                                                                                             select 'quan', N'勸'
                                                                                                             union all
                                                                                                             select 'que', N'礭'
                                                                                                             union all
                                                                                                             select 'qun', N'囕'
                                                                                                             union all
                                                                                                             select 'ran', N'橪'
                                                                                                             union all
                                                                                                             select 'rang', N'讓'
                                                                                                             union all
                                                                                                             select 'rao', N'繞'
                                                                                                             union all
                                                                                                             select 're', N'熱'
                                                                                                             union all
                                                                                                             select 'ren', N'餁'
                                                                                                             union all
                                                                                                             select 'reng', N'陾'
                                                                                                             union all
                                                                                                             select 'ri', N'馹'
                                                                                                             union all
                                                                                                             select 'rong', N'穃'
                                                                                                             union all
                                                                                                             select 'rou', N'嶿'
                                                                                                             union all
                                                                                                             select 'ru', N'擩'
                                                                                                             union all
                                                                                                             select 'ruan', N'礝'
                                                                                                             union all
                                                                                                             select 'rui', N'壡'
                                                                                                             union all
                                                                                                             select 'run', N'橍' --橍挼  
                                                                                                             union all
                                                                                                             select 'ruo', N'鶸'
                                                                                                             union all
                                                                                                             select 'sa', N'栍' --櫒栍  
                                                                                                             union all
                                                                                                             select 'sai', N'虄' --簺虄  
                                                                                                             union all
                                                                                                             select 'san', N'閐'
                                                                                                             union all
                                                                                                             select 'sang', N'喪'
                                                                                                             union all
                                                                                                             select 'sao', N'髞'
                                                                                                             union all
                                                                                                             select 'se', N'飋' --裇聓  
                                                                                                             union all
                                                                                                             select 'sen', N'篸'
                                                                                                             union all
                                                                                                             select 'seng', N'縇' --閪縇  
                                                                                                             union all
                                                                                                             select 'sha', N'霎'
                                                                                                             union all
                                                                                                             select 'shai', N'曬'
                                                                                                             union all
                                                                                                             select 'shan', N'鱔'
                                                                                                             union all
                                                                                                             select 'shang', N'緔'
                                                                                                             union all
                                                                                                             select 'shao', N'潲'
                                                                                                             union all
                                                                                                             select 'she', N'欇'
                                                                                                             union all
                                                                                                             select 'shen', N'瘮'
                                                                                                             union all
                                                                                                             select 'sheng', N'賸'
                                                                                                             union all
                                                                                                             select 'shi', N'瓧' --鰘齛兙瓧  
                                                                                                             union all
                                                                                                             select 'shou', N'鏉'
                                                                                                             union all
                                                                                                             select 'shu', N'虪'
                                                                                                             union all
                                                                                                             select 'shua', N'誜'
                                                                                                             union all
                                                                                                             select 'shuai', N'卛'
                                                                                                             union all
                                                                                                             select 'shuan', N'腨'
                                                                                                             union all
                                                                                                             select 'shuang', N'灀'
                                                                                                             union all
                                                                                                             select 'shui', N'睡'
                                                                                                             union all
                                                                                                             select 'shun', N'鬊'
                                                                                                             union all
                                                                                                             select 'shuo', N'鑠'
                                                                                                             union all
                                                                                                             select 'si', N'乺' --瀃螦乺  
                                                                                                             union all
                                                                                                             select 'song', N'鎹'
                                                                                                             union all
                                                                                                             select 'sou', N'瘶'
                                                                                                             union all
                                                                                                             select 'su', N'鷫'
                                                                                                             union all
                                                                                                             select 'suan', N'算'
                                                                                                             union all
                                                                                                             select 'sui', N'鐩'
                                                                                                             union all
                                                                                                             select 'sun', N'潠'
                                                                                                             union all
                                                                                                             select 'suo', N'蜶'
                                                                                                             union all
                                                                                                             select 'ta', N'襨' --躢襨  
                                                                                                             union all
                                                                                                             select 'tai', N'燤'
                                                                                                             union all
                                                                                                             select 'tan', N'賧'
                                                                                                             union all
                                                                                                             select 'tang', N'燙'
                                                                                                             union all
                                                                                                             select 'tao', N'畓' --討畓  
                                                                                                             union all
                                                                                                             select 'te', N'蟘'
                                                                                                             union all
                                                                                                             select 'teng', N'朰' --霯唞朰  
                                                                                                             union all
                                                                                                             select 'ti', N'趯'
                                                                                                             union all
                                                                                                             select 'tian', N'舚'
                                                                                                             union all
                                                                                                             select 'tiao', N'糶'
                                                                                                             union all
                                                                                                             select 'tie', N'餮'
                                                                                                             union all
                                                                                                             select 'ting', N'乭' --濎乭  
                                                                                                             union all
                                                                                                             select 'tong', N'憅'
                                                                                                             union all
                                                                                                             select 'tou', N'透'
                                                                                                             union all
                                                                                                             select 'tu', N'鵵'
                                                                                                             union all
                                                                                                             select 'tuan', N'褖'
                                                                                                             union all
                                                                                                             select 'tui', N'駾'
                                                                                                             union all
                                                                                                             select 'tun', N'坉'
                                                                                                             union all
                                                                                                             select 'tuo', N'籜'
                                                                                                             union all
                                                                                                             select 'wa', N'韤'
                                                                                                             union all
                                                                                                             select 'wai', N'顡'
                                                                                                             union all
                                                                                                             select 'wan', N'贎'
                                                                                                             union all
                                                                                                             select 'wang', N'朢'
                                                                                                             union all
                                                                                                             select 'wei', N'躛'
                                                                                                             union all
                                                                                                             select 'wen', N'璺'
                                                                                                             union all
                                                                                                             select 'weng', N'齆'
                                                                                                             union all
                                                                                                             select 'wo', N'齷'
                                                                                                             union all
                                                                                                             select 'wu', N'鶩'
                                                                                                             union all
                                                                                                             select 'xi', N'衋'
                                                                                                             union all
                                                                                                             select 'xia', N'鏬'
                                                                                                             union all
                                                                                                             select 'xian', N'鼸'
                                                                                                             union all
                                                                                                             select 'xiang', N'鱌'
                                                                                                             union all
                                                                                                             select 'xiao', N'斆'
                                                                                                             union all
                                                                                                             select 'xie', N'躞'
                                                                                                             union all
                                                                                                             select 'xin', N'釁'
                                                                                                             union all
                                                                                                             select 'xing', N'臖'
                                                                                                             union all
                                                                                                             select 'xiong', N'敻'
                                                                                                             union all
                                                                                                             select 'xiu', N'齅'
                                                                                                             union all
                                                                                                             select 'xu', N'蓿'
                                                                                                             union all
                                                                                                             select 'xuan', N'贙'
                                                                                                             union all
                                                                                                             select 'xue', N'瀥'
                                                                                                             union all
                                                                                                             select 'xun', N'鑂'
                                                                                                             union all
                                                                                                             select 'ya', N'齾'
                                                                                                             union all
                                                                                                             select 'yan', N'灩'
                                                                                                             union all
                                                                                                             select 'yang', N'樣'
                                                                                                             union all
                                                                                                             select 'yao', N'鑰'
                                                                                                             union all
                                                                                                             select 'ye', N'岃' --鸈膶岃  
                                                                                                             union all
                                                                                                             select 'yi', N'齸'
                                                                                                             union all
                                                                                                             select 'yin', N'檼'
                                                                                                             union all
                                                                                                             select 'ying', N'譍'
                                                                                                             union all
                                                                                                             select 'yo', N'喲'
                                                                                                             union all
                                                                                                             select 'yong', N'醟'
                                                                                                             union all
                                                                                                             select 'you', N'鼬'
                                                                                                             union all
                                                                                                             select 'yu', N'爩'
                                                                                                             union all
                                                                                                             select 'yuan', N'願'
                                                                                                             union all
                                                                                                             select 'yue', N'鸙'
                                                                                                             union all
                                                                                                             select 'yun', N'韻'
                                                                                                             union all
                                                                                                             select 'za', N'雥'
                                                                                                             union all
                                                                                                             select 'zai', N'縡'
                                                                                                             union all
                                                                                                             select 'zan', N'饡'
                                                                                                             union all
                                                                                                             select 'zang', N'臟'
                                                                                                             union all
                                                                                                             select 'zao', N'竈'
                                                                                                             union all
                                                                                                             select 'ze', N'稄'
                                                                                                             union all
                                                                                                             select 'zei', N'鱡'
                                                                                                             union all
                                                                                                             select 'zen', N'囎'
                                                                                                             union all
                                                                                                             select 'zeng', N'贈'
                                                                                                             union all
                                                                                                             select 'zha', N'醡'
                                                                                                             union all
                                                                                                             select 'zhai', N'瘵'
                                                                                                             union all
                                                                                                             select 'zhan', N'驏'
                                                                                                             union all
                                                                                                             select 'zhang', N'瞕'
                                                                                                             union all
                                                                                                             select 'zhao', N'羄'
                                                                                                             union all
                                                                                                             select 'zhe', N'鷓'
                                                                                                             union all
                                                                                                             select 'zhen', N'黮'
                                                                                                             union all
                                                                                                             select 'zheng', N'證'
                                                                                                             union all
                                                                                                             select 'zhi', N'豒'
                                                                                                             union all
                                                                                                             select 'zhong', N'諥'
                                                                                                             union all
                                                                                                             select 'zhou', N'驟'
                                                                                                             union all
                                                                                                             select 'zhu', N'鑄'
                                                                                                             union all
                                                                                                             select 'zhua', N'爪'
                                                                                                             union all
                                                                                                             select 'zhuai', N'跩'
                                                                                                             union all
                                                                                                             select 'zhuan', N'籑'
                                                                                                             union all
                                                                                                             select 'zhuang', N'戅'
                                                                                                             union all
                                                                                                             select 'zhui', N'鑆'
                                                                                                             union all
                                                                                                             select 'zhun', N'稕'
                                                                                                             union all
                                                                                                             select 'zhuo', N'籱'
                                                                                                             union all
                                                                                                             select 'zi', N'漬' --漬唨  
                                                                                                             union all
                                                                                                             select 'zong', N'縱'
                                                                                                             union all
                                                                                                             select 'zou', N'媰'
                                                                                                             union all
                                                                                                             select 'zu', N'謯'
                                                                                                             union all
                                                                                                             select 'zuan', N'攥'
                                                                                                             union all
                                                                                                             select 'zui', N'欈'
                                                                                                             union all
                                                                                                             select 'zun', N'銌'
                                                                                                             union all
                                                                                                             select 'zuo', N'咗') t
                                                                                                        where word >= @word collate Chinese_PRC_CS_AS_KS_WS
                                                                                                        order by word collate Chinese_PRC_CS_AS_KS_WS asc)else @word
                                           end);
        set @i = @i+1;
    end;
    return upper(@pinyin);
end;
GO
/****** Object:  UserDefinedFunction [dbo].[fn_getPy]    Script Date: 2022/2/15 11:52:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[fn_getPy](@str nvarchar(4000))
returns varchar(8000)
as
begin
declare @word nchar(1),@PY varchar(8000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T 
where word>=@word collate Chinese_PRC_CS_AS_KS_WS 
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end
GO
/****** Object:  UserDefinedFunction [dbo].[fn_replaceNum]    Script Date: 2022/2/15 11:52:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_replaceNum](@str varchar(500),@canSee bit)
returns varchar(500)
as begin
	if(@str is null) return '';
	if(@canSee is null) set @canSee=0;
	if(@canSee=1) return @str;
    declare @r varchar(500);
    declare @len int;
    set @len = len(@str);
    select @r = (case when @len = 18 then stuff(@str, 9, @len-14, replicate('*', @len-14))
                 when @len > 9 then stuff(@str, 5, @len-7, replicate('*', @len-7))
                 when @len > 7 then stuff(@str, 4, @len-5, replicate('*', @len-5))
                 when @len > 4 then stuff(@str, 3, @len-4, replicate('*', @len-4))
                 when @len > 2 then stuff(@str, 2, @len-2, replicate('*', @len-2))else @str
                 end);
    return @r;
end;

GO
/****** Object:  UserDefinedFunction [dbo].[fn_splitStr]    Script Date: 2022/2/15 11:52:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[fn_splitStr](@s varchar(8000) --待分拆的字符串
, @split varchar(10) --数据分隔符
)
returns @re table(col varchar(100))
as begin
    declare @splitlen int;
    set @splitlen = len(@split+'a')-2;
    while charindex(@split, @s) > 0 begin
        insert @re values(left(@s, charindex(@split, @s)-1));
        set @s = stuff(@s, 1, charindex(@split, @s)+@splitlen, '');
    end;
    insert @re values(@s);
    return;
end;
GO
